"""
Case Type   : SQL_DDL_Drop_Owned
Case Name   : REASSIGN OWNED修改的对象类型验证
Create At   : 2024/05/21
Owner       : liu-tong-8848
Description :
    1.创建用户
    2.用户1创建表
    3.用户1创建视图
    4.用户1创建索引
    5.用户1创建序列
    6.用户1创建目录对象
    7.用户1创建函数
    8.用户1创建触发器
    9.用户1创建存储过程
    10.用户1创建模式
    11.修改用户1所拥有的对象，查询是否修改成功
    12.删除数据库对象，删除用户，恢复参数
Expect      :
    1.创建成功
    2.创建成功
    3.创建成功
    4.创建成功
    5.创建成功
    6.创建成功
    7.创建成功
    8.创建成功
    9.创建成功
    10.创建成功
    11.除触发器外均修改成功
    12.删除成功，恢复成功
History     :
"""
import os
import unittest
from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Common import Common
from yat.test import Node
from yat.test import macro


class DropOwned(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        self.logger.info(f"-----{os.path.basename(__file__)} start-----")
        self.user_node = Node('PrimaryDbUser')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.common = Common()
        self.user_name_1 = 'u_reassign_owned_0008_1'
        self.user_name_2 = 'u_reassign_owned_0008_2'
        self.table_name = 't_reassign_owned_0008'
        self.view_name = 'v_reassign_owned_0008'
        self.index_name = 'i_reassign_owned_0008'
        self.seq_name = 'seq_reassign_owned_0008'
        self.dir_object_name = 'dir_object_reassign_owned_0008'
        self.func_name = 'func_reassign_owned_0008'
        self.trigger_name = 'trigger_reassign_owned_0008'
        self.procedure_name = 'procedure_reassign_owned_0008'
        self.schema_name = 'schema_reassign_owned_0008'
        self.config_directory = 'enable_access_server_directory'
        self.directory_default = self.common.show_param(self.config_directory)

    def test_drop_owned(self):
        text = "-----step1:创建用户;expect:创建成功-----"
        self.logger.info(text)
        create_user = f"drop user if exists {self.user_name_1}," \
            f"{self.user_name_2};" \
            f"create user {self.user_name_1} " \
            f"sysadmin password '{macro.COMMON_PASSWD}';" \
            f"create user {self.user_name_2} " \
            f"sysadmin password '{macro.COMMON_PASSWD}';"
        create_msg = self.primary_sh.execut_db_sql(create_user)
        self.logger.info(create_msg)
        self.assertIn(self.constant.DROP_ROLE_SUCCESS_MSG,
                      create_msg, '执行失败' + text)
        self.assertEquals(2, create_msg.count(
            self.constant.CREATE_ROLE_SUCCESS_MSG), '执行失败' + text)

        text = "-----step2:用户1创建表;expect:创建成功-----"
        self.logger.info(text)
        sql_cmd = f"drop table if exists {self.table_name};" \
            f"create table {self.table_name}(id int, name varchar(15));" \
            f"insert into {self.table_name} " \
            f"values(1, '软件开发'),(2, '软件测试');\\d"
        sql_msg = self.primary_sh.execut_db_sql(
            sql_cmd, f'-U {self.user_name_1} -W \'{macro.COMMON_PASSWD}\'')
        self.logger.info(sql_msg)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS and
                      self.constant.TABLE_CREATE_SUCCESS and
                      self.constant.INSERT_SUCCESS_MSG,
                      sql_msg, '执行失败' + text)
        self.assertIn(self.table_name and self.user_name_1,
                      sql_msg.splitlines()[-2], '执行失败' + text)

        text = "-----step3:用户1创建视图;expect:创建成功-----"
        self.logger.info(text)
        sql_cmd = f"drop view if exists {self.view_name};" \
            f"create view {self.view_name} as " \
            f"select * from {self.table_name};" \
            f"\\d"
        sql_msg = self.primary_sh.execut_db_sql(
            sql_cmd, f'-U {self.user_name_1} -W \'{macro.COMMON_PASSWD}\'')
        self.logger.info(sql_msg)
        self.assertIn(self.constant.DROP_VIEW_SUCCESS_MSG and
                      self.constant.CREATE_VIEW_SUCCESS_MSG,
                      sql_msg, '执行失败' + text)
        self.assertIn(self.view_name and self.user_name_1,
                      sql_msg.splitlines()[-2], '执行失败' + text)

        text = "-----step4:用户1创建索引;expect:创建成功-----"
        self.logger.info(text)
        sql_cmd = f"drop index if exists {self.index_name};" \
            f"create unique index {self.index_name} on {self.table_name}(id);" \
            f"\\di"
        sql_msg = self.primary_sh.execut_db_sql(
            sql_cmd, f'-U {self.user_name_1} -W \'{macro.COMMON_PASSWD}\'')
        self.logger.info(sql_msg)
        self.assertIn(self.constant.DROP_INDEX_SUCCESS_MSG and
                      self.constant.CREATE_INDEX_SUCCESS_MSG,
                      sql_msg, '执行失败' + text)
        self.assertIn(self.index_name and self.user_name_1,
                      sql_msg.splitlines()[-2], '执行失败' + text)

        text = "-----step5:用户1创建序列;expect:创建成功-----"
        self.logger.info(text)
        sql_cmd = f"drop sequence if exists {self.seq_name};" \
            f"create sequence {self.seq_name} start 101 cache 20;\\d"
        sql_msg = self.primary_sh.execut_db_sql(
            sql_cmd, f'-U {self.user_name_1} -W \'{macro.COMMON_PASSWD}\'')
        self.logger.info(sql_msg)
        self.assertIn(self.constant.DROP_SEQUENCE_SUCCESS_MSG and
                      self.constant.CREATE_SEQUENCE_SUCCESS_MSG,
                      sql_msg, '执行失败' + text)
        self.assertIn(self.seq_name and self.user_name_1,
                      sql_msg.splitlines()[-4], '执行失败' + text)

        text = "-----step6:用户1创建目录对象;expect:创建成功-----"
        self.logger.info(text)
        if 'on' != self.directory_default:
            result = self.primary_sh.execute_gsguc(
                'reload', self.constant.GSGUC_SUCCESS_MSG,
                f'{self.config_directory}=on')
            self.assertTrue(result, '执行失败' + text)
        show_msg = self.common.show_param(self.config_directory)
        self.assertEquals(show_msg, 'on', '执行失败' + text)
        sql_cmd = f'''create or replace directory {self.dir_object_name}
            as '/tmp';
            select t1.dirname, t2.usename, t1.dirpath from pg_directory t1
            inner join pg_user t2 on t2.usesysid=t1.owner
            where dirname='{self.dir_object_name}';'''
        sql_msg = self.primary_sh.execut_db_sql(
            sql_cmd, f'-U {self.user_name_1} -W \'{macro.COMMON_PASSWD}\'')
        self.logger.info(sql_msg)
        self.assertIn(self.constant.CREATE_DIRECTORY_SUCCESS_MSG,
                      sql_msg, '执行失败' + text)
        self.assertIn(self.dir_object_name and self.user_name_1,
                      sql_msg.splitlines()[-2], '执行失败' + text)

        text = "-----step7:用户1创建函数;expect:创建成功-----"
        self.logger.info(text)
        sql_cmd = f'''create or replace function {self.func_name}() 
        returns trigger as 
        \$\$ 
        declare 
        begin 
            update {self.table_name} set name='开发工程师' where id=2; 
        return new;
        end 
        \$\$ language plpgsql;
        select t1.proname, t2.usename from pg_proc t1 inner join pg_user t2 
        on t2.usesysid=t1.proowner where proname='{self.func_name}';'''
        sql_msg = self.primary_sh.execut_db_sql(
            sql_cmd, f'-U {self.user_name_1} -W \'{macro.COMMON_PASSWD}\'')
        self.logger.info(sql_msg)
        self.assertIn(self.constant.CREATE_FUNCTION_SUCCESS_MSG,
                      sql_msg, '执行失败' + text)
        self.assertIn(self.func_name and self.user_name_1,
                      sql_msg.splitlines()[-2], '执行失败' + text)

        text = "-----step8:用户1创建触发器;expect:创建成功-----"
        self.logger.info(text)
        sql_cmd = f'''drop trigger if exists {self.trigger_name} 
            on {self.table_name};
            create trigger {self.trigger_name} 
            after insert on {self.table_name} for each row 
            execute procedure {self.func_name}();
            select t1.tgname, t2.usename  from pg_trigger t1 
            inner join pg_user t2 on t2.usesysid=t1.tgowner 
            where tgname='{self.trigger_name}';'''
        sql_msg = self.primary_sh.execut_db_sql(
            sql_cmd, f'-U {self.user_name_1} -W \'{macro.COMMON_PASSWD}\'')
        self.logger.info(sql_msg)
        self.assertIn(self.constant.TRIGGER_CREATE_SUCCESS_MSG,
                      sql_msg, '执行失败' + text)
        self.assertIn(self.trigger_name and self.user_name_1,
                      sql_msg.splitlines()[-2], '执行失败' + text)

        text = "-----step9:用户1创建存储过程;expect:创建成功-----"
        self.logger.info(text)
        sql_cmd = f'''create or replace procedure 
        {self.procedure_name}(tb_name varchar) is 
        begin 
            execute immediate 'insert into  '|| tb_name || ' values(3,''西安'');';
        end;'''
        sql_msg = self.primary_sh.execut_db_sql(
            sql_cmd, f'-U {self.user_name_1} -W \'{macro.COMMON_PASSWD}\'')
        self.logger.info(sql_msg)
        self.assertIn(self.constant.CREATE_PROCEDURE_SUCCESS_MSG,
                      sql_msg, '执行失败' + text)
        sql_cmd = f'''select t1.proname, t2.usename 
            from pg_proc t1 inner join pg_user t2 on t2.usesysid=t1.proowner 
            where proname='{self.procedure_name}';'''
        sql_msg = self.primary_sh.execut_db_sql(
            sql_cmd, f'-U {self.user_name_1} -W \'{macro.COMMON_PASSWD}\'')
        self.logger.info(sql_msg)
        self.assertIn(self.procedure_name and self.user_name_1,
                      sql_msg.splitlines()[-2], '执行失败' + text)

        text = "-----step10:用户1创建模式;expect:创建成功-----"
        self.logger.info(text)
        sql_cmd = f'''drop schema if exists {self.schema_name};
            create schema {self.schema_name};
            select t1.nspname, t2.usename from pg_namespace t1 
            inner join pg_user t2 on t2.usesysid=t1.nspowner 
            where t1.nspname='{self.schema_name}';'''
        sql_msg = self.primary_sh.execut_db_sql(
            sql_cmd, f'-U {self.user_name_1} -W \'{macro.COMMON_PASSWD}\'')
        self.logger.info(sql_msg)
        self.assertIn(self.constant.DROP_SCHEMA_SUCCESS_MSG and
                      self.constant.CREATE_SCHEMA_SUCCESS_MSG,
                      sql_msg, '执行失败' + text)
        self.assertIn(self.schema_name and self.user_name_1,
                      sql_msg.splitlines()[-2], '执行失败' + text)

        text = "-----step11:修改用户1所拥有的对象，查询是否修改成功;expect:除触发器外均修改成功-----"
        self.logger.info(text)
        sql_cmd_1 = f"reassign owned by {self.user_name_1} " \
            f"to {self.user_name_2};\\d"
        sql_msg_1 = self.primary_sh.execut_db_sql(
            sql_cmd_1, f'-U {self.user_name_1} -W \'{macro.COMMON_PASSWD}\'')
        self.logger.info(sql_msg_1)
        self.assertIn(self.constant.reassign_owned_success,
                      sql_msg_1, '执行失败' + text)
        self.assertIn(self.seq_name and self.user_name_1,
                      sql_msg_1.splitlines()[-4], '执行失败' + text)
        self.assertIn(self.table_name and self.user_name_1,
                      sql_msg_1.splitlines()[-3], '执行失败' + text)
        self.assertIn(self.view_name and self.user_name_1,
                      sql_msg_1.splitlines()[-2], '执行失败' + text)
        sql_cmd_2 = f'''select dirname, t2.usename from pg_directory t1 
            inner join pg_user t2 on t2.usesysid=t1.owner 
            where dirname='{self.dir_object_name}';
            select t1.proname, t2.usename from pg_proc t1 inner join pg_user t2 
            on t2.usesysid=t1.proowner where proname='{self.func_name}';
            select t1.tgname, t2.usename  from pg_trigger t1 
            inner join pg_user t2 on t2.usesysid=t1.tgowner 
            where tgname='{self.trigger_name}';
            select t1.proname, t2.usename from pg_proc t1 inner join pg_user t2 
            on t2.usesysid=t1.proowner where proname='{self.procedure_name}';
            select t1.nspname, t2.usename from pg_namespace t1 
            inner join pg_user t2 on t2.usesysid=t1.nspowner 
            where t1.nspname='{self.schema_name}';
            \\di'''
        sql_msg_2 = self.primary_sh.execut_db_sql(
            sql_cmd_2, f'-U {self.user_name_1} -W \'{macro.COMMON_PASSWD}\'')
        self.logger.info(sql_msg_2)
        self.assertIn(self.dir_object_name and self.user_name_2,
                      sql_msg_2.splitlines()[2], '执行失败' + text)
        self.assertIn(self.func_name and self.user_name_2,
                      sql_msg_2.splitlines()[7], '执行失败' + text)
        self.assertIn(self.trigger_name and self.user_name_1,
                      sql_msg_2.splitlines()[-18], '执行失败' + text)
        self.assertIn(self.procedure_name and self.user_name_2,
                      sql_msg_2.splitlines()[-13], '执行失败' + text)
        self.assertIn(self.schema_name and self.user_name_2,
                      sql_msg_2.splitlines()[-8], '执行失败' + text)
        self.assertIn(self.index_name and self.user_name_2,
                      sql_msg_2.splitlines()[-2], '执行失败' + text)

    def tearDown(self):
        text = "-----step12:删除用户，恢复参数;expect:删除成功，恢复成功-----"
        self.logger.info(text)
        drop_cmd = f"drop owned by {self.user_name_2} cascade;" \
            f"drop user {self.user_name_1} cascade;" \
            f"drop user {self.user_name_2} cascade;"
        drop_msg = self.primary_sh.execut_db_sql(drop_cmd)
        self.logger.info(drop_msg)
        current = self.common.show_param(self.config_directory)
        if self.directory_default != current:
            self.primary_sh.execute_gsguc(
                'reload', self.constant.GSGUC_SUCCESS_MSG,
                f'{self.config_directory}={self.directory_default}')
        show_msg = self.common.show_param(self.config_directory)
        self.assertIn(self.constant.DROP_OWNED_SUCCESS,
                      drop_msg, '执行失败' + text)
        self.assertEquals(2, drop_msg.count(
            self.constant.DROP_ROLE_SUCCESS_MSG), '执行失败' + text)
        self.assertEquals(show_msg, self.directory_default, '执行失败' + text)
        self.logger.info(f"-----{os.path.basename(__file__)} end-----")
